package com.aaa.project.zhreport.service.impl;

import cn.hutool.core.util.StrUtil;
import com.aaa.common.exception.ServiceException;
import com.aaa.project.file.service.ISysFileService;
import com.aaa.project.zhreport.domain.YwfxDataSources;
import com.aaa.project.zhreport.domain.YwfxDataTemplateConfig;
import com.aaa.project.zhreport.enums.FileStatusEnum;
import com.aaa.project.zhreport.mapper.YwfxDataSourcesMapper;
import com.aaa.project.zhreport.mapper.YwfxDataTemplateConfigMapper;
import com.aaa.project.zhreport.mapper.YwfxFileDownloadRecordMapper;
import com.aaa.project.zhreport.service.DataFetchService;
import com.alibaba.druid.util.JdbcUtils;
import org.apache.commons.io.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @author baibin
 * @version 1.0
 * @date 2025/4/11 8:40
 * @description DataFetchServiceImpl
 */
@Service
public class DataFetchServiceImpl implements DataFetchService {

    private static final Logger log = LoggerFactory.getLogger(DataFetchServiceImpl.class);

    @Resource
    private YwfxDataTemplateConfigMapper ywfxDataTemplateConfigMapper;
    @Resource
    private YwfxDataSourcesMapper ywfxDataSourcesMapper;
    @Resource
    private YwfxFileDownloadRecordMapper ywfxFileDownloadRecordMapper;
    @Resource
    private ISysFileService sysFileService;

    @Resource(name = "billOracleDataSource")
    private DataSource billOracleDataSource;

    @Resource(name = "authOracleDataSource")
    private DataSource authOracleDataSource;

    @Resource(name = "billPGDataSource")
    private DataSource billPGDataSource;


    @Async
    @Override
    public void asyncFetchDataByTemplateId(Long templateId, Long fileDownloadRecordId, String batchNumber, String sql) {
        log.info("the data is asynchronously obtained ，模板ID：{}，文件下载记录ID：{}", templateId, fileDownloadRecordId);
        // 根据模板ID获取数据
        YwfxDataTemplateConfig templateConfig = ywfxDataTemplateConfigMapper.findById(templateId);
        if (templateConfig == null) {
            log.error("the template config is not found，模板ID：{}", templateId);
            // 更新文件下载记录的状态为失败
            updateStatus(fileDownloadRecordId, FileStatusEnum.FAIL.getCode(), "模板配置不存在");
            return;
        }
        // 根据模板id绑定的数据源，获取数据源配置信息
        YwfxDataSources dataSources = ywfxDataSourcesMapper.findById(templateConfig.getDataSourceId());
        if (dataSources == null) {
            log.error("the data source is not found，数据源ID：{}", templateConfig.getDataSourceId());
            // 更新文件下载记录的状态为失败
            updateStatus(fileDownloadRecordId, FileStatusEnum.FAIL.getCode(), "数据源配置不存在");
            return;
        }

        // 根据类型判断数据源类型，并建立JDBC连接
        Connection connection = connectToDatabase(dataSources, fileDownloadRecordId);
        // 执行sql，获取数据集
        executeSql(connection, dataSources, templateConfig, fileDownloadRecordId, batchNumber, sql);
    }

    @Override
    public void preExecuteSql(String sql, YwfxDataSources dataSource) {
        log.info("the SQL is pre-executed，数据源配置：{}，SQL：{}", dataSource.getDataSourceName(), sql);
        log.info("Pre-executing SQL: {}", sql);
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            // 1. 获取数据库连接
            connection = connectToDatabase(dataSource, null);
            if (connection == null) {
                throw new RuntimeException("Failed to establish database connection.");
            }
            // 3. 预执行 SQL（带 LIMIT）
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            log.info("SQL pre-execution succeeded.");
        } catch (Exception e) {
            log.error("SQL pre-execution sql: {} failed: {}", sql, e.getMessage(), e);
            throw new ServiceException("执行SQL失败，请检查SQL语法或联系管理员！");
        } finally {
            // 关闭资源
            JdbcUtils.close(resultSet);
            JdbcUtils.close(preparedStatement);
            JdbcUtils.close(connection);
        }
    }

    /**
     * 更新文件下载记录的状态
     *
     * @param fileDownloadRecordId
     * @param status
     * @param message
     */
    private void updateStatus(Long fileDownloadRecordId, String status, String message) {
        log.info("the file download record status is updated，文件下载记录ID：{}，状态：{}，消息：{}", fileDownloadRecordId, status, message);
        // 更新文件下载记录的状态
        ywfxFileDownloadRecordMapper.updateStatus(fileDownloadRecordId, status, message);
        log.info("the file download record status is updated successfully.");
    }


    /**
     * 更新文件下载记录的状态为成功
     *
     * @param fileDownloadRecordId
     * @param fileName
     * @param fileUrl
     */
    private void updateStatusSuccess(Long fileDownloadRecordId, int fileRowCount, String fileName, String fileUrl) {
        log.info("the file download record status is updated，文件下载记录ID：{}，文件行数：{}，文件名：{}，文件URL：{}", fileDownloadRecordId, fileRowCount, fileName, fileUrl);
        ywfxFileDownloadRecordMapper.updateStatusSuccess(fileDownloadRecordId, fileRowCount, fileName, fileUrl);
    }

    /**
     * 建立数据库连接方法
     *
     * @param dataSources
     */
    private Connection connectToDatabase(YwfxDataSources dataSources, Long fileDownloadRecordId) {
        log.info("the JDBC connection is established，数据源配置：{}", dataSources.getDataSourceName());
        // 根据类型判断数据源类型，并建立JDBC连接
        Connection connection = null;
        try {
            // 根据数据源ip、端口、数据库名组装数据源URL
            String dbType = dataSources.getDataSourceName(); // 数据源类型（如 MYSQL、ORACLE、POSTGRESQL）

            // 根据数据源类型加载对应的 JDBC 驱动
            switch (dataSources.getDataSourceType()) {
                case "ORACLE":
                    // 区分数据库
                    if ("BILL".equals(dataSources.getDatabaseName())) {
                        connection = billOracleDataSource.getConnection();
                    } else if ("RADIUS".equals(dataSources.getDatabaseName())) {
                        connection = authOracleDataSource.getConnection();
                    } else {
                        log.error("the database name is not found，数据库名：{}", dataSources.getDatabaseName());
                        // 更新文件下载记录的状态为失败
                        updateStatus(fileDownloadRecordId, FileStatusEnum.FAIL.getCode(), "数据库名不存在");
                        return null;
                    }
                    break;
                case "POSTGRESQL":
                    connection = billPGDataSource.getConnection();
                    break;
            }
            log.info("JDBC connection established successfully for database type: {}", dbType);
        } catch (Exception e) {
            log.error("failed to establish JDBC connection，错误信息：{}", e);
            if (fileDownloadRecordId != null) {
                // 处理异常，例如记录日志或抛出异常
                updateStatus(fileDownloadRecordId, FileStatusEnum.FAIL.getCode(),
                        StrUtil.format("数据源：{}，连接数据库失败！", dataSources.getDataSourceName()));
            }
        }
        return connection;
    }

    private void executeSql(Connection connection, YwfxDataSources dataSources, YwfxDataTemplateConfig templateConfig, Long fileDownloadRecordId,
                            String batchNumber, String sql) {
        log.info("the SQL is executed，数据库名：{}，模板配置：{} 批次号：{}", dataSources.getDataSourceName(), templateConfig.getTemplateName(), batchNumber);
        // 判断数据源类型执行不同的SQL语句
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ByteArrayInputStream byteArrayInputStream = null;
        try {
            log.info("exec SQL：{}", sql);
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            // 处理查询结果
            AtomicInteger fileRowCount = new AtomicInteger(0);
            byteArrayInputStream = convertResultSetToCsvStream(resultSet, fileRowCount);
            // 上传文件
            String fileName = StrUtil.format("{}.csv", batchNumber);
            String fileUrl = sysFileService.uploadFile(byteArrayInputStream, fileName);
            // 更新文件下载记录的状态为成功
            updateStatusSuccess(fileDownloadRecordId, fileRowCount.get(), fileName, fileUrl);
        } catch (Exception e) {
            log.error("failed to execute SQL，错误信息：{}", e);
            // 处理异常，例如记录日志或抛出异常
            updateStatus(fileDownloadRecordId, FileStatusEnum.FAIL.getCode(),
                    StrUtil.format("数据源：{}，执行SQL失败，内容：{}", dataSources.getDataSourceName(), sql));
        } finally {
            // 关闭流
            IOUtils.closeQuietly(byteArrayInputStream);
            JdbcUtils.close(resultSet);
            JdbcUtils.close(preparedStatement);
            JdbcUtils.close(connection);
        }
    }


    /**
     * 将ResultSet转换为CSV格式的ByteArrayInputStream
     */
    private ByteArrayInputStream convertResultSetToCsvStream(ResultSet resultSet, AtomicInteger fileRowCount) throws SQLException, IOException {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        OutputStreamWriter writer = new OutputStreamWriter(baos, StandardCharsets.UTF_8);
        try {
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            int rowCount = 0;
            // 写入CSV表头
            for (int i = 1; i <= columnCount; i++) {
                writer.write(escapeCsvValue(metaData.getColumnName(i)));
                if (i < columnCount) {
                    writer.write(",");
                }
            }
            writer.write("\n");

            // 写入数据行
            while (resultSet.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    // 显式转换字符集
                    String string = resultSet.getString(i);
                    String csvValue = escapeCsvValue(resultSet.getString(i));
                    writer.write(csvValue);
                    if (i < columnCount) {
                        writer.write(",");
                    }
                }
                writer.write("\n");
                rowCount++;
            }
            writer.flush();
            fileRowCount.set(rowCount);
            return new ByteArrayInputStream(baos.toByteArray());
        } finally {
            IOUtils.close(writer);
            IOUtils.close(baos);
        }
    }

    /**
     * 转义CSV特殊字符
     */
    private String escapeCsvValue(String value) {
        if (value == null) {
            return "";
        }
        if (value.contains(",") || value.contains("\"") || value.contains("\n")) {
            return "\"" + value.replace("\"", "\"\"") + "\"";
        }
        return value;
    }

}
